package cc.comac.mom.util;

import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.DataFormatter;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;
public class ExcelUploadUtil {
	private static final String EXCEL_XLS = "xls";
	private static final String EXCEL_XLSX = "xlsx";

	
	public static Workbook getWorkBook(MultipartFile file) {
		Workbook workbook = null;
		  
		  InputStream inputStream = null;
	        try {
	            inputStream = file.getInputStream();
	        } catch (IOException e) {
	            throw new RuntimeException("文件转化流失败!");
	        }
	        try {
	            String fileName = file.getOriginalFilename();
	            if (fileName.toLowerCase().endsWith("xls")) {
	                workbook = new HSSFWorkbook(inputStream);
	            } else if (fileName.toLowerCase().endsWith("xlsx")) {
	                workbook = new XSSFWorkbook(inputStream);
	            } else {
	                throw new RuntimeException("上传的文件需为Excel文件!");
	            }
	        } catch (IOException e) {
	            throw new RuntimeException("解析Excel文件失败!");
	        } catch (Exception e) {
	            throw new RuntimeException(e.getMessage());
	        }
		return workbook;
	}
	
	public static File validateImportDate (MultipartFile file, String rootPath) {
		if (file == null || file.isEmpty()) {
			throw new RuntimeException("文件上传失败，文件为空！");
		} else if (file.getSize() > 10000000) {
			throw new RuntimeException("文件上传失败，文件大小不能大于10");
		}
		String fileName = file.getOriginalFilename();
		File dir = new File(rootPath);
		if (!dir.exists()) {
			dir.mkdir();
		}
		File chunkFile = new File(dir, fileName);
		try {
			chunkFile.createNewFile();
		} catch (IOException e) {
			e.printStackTrace();
		}
		try {
			file.transferTo(chunkFile);
		} catch (IllegalStateException | IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return chunkFile;
	}
	
	
	
	/**
	 * 判断Excel的版本,获取Workbook
	 * 
	 * @param in
	 *            File
	 * @param file
	 *            File
	 * @return Workbook
	 */
	public static Workbook getWorkbok(InputStream in, File file) throws IOException {
		Workbook wb = null;
		// Excel 2003
		if (file.getName().endsWith(EXCEL_XLS)) {
			wb = new HSSFWorkbook(in);
			// Excel 2007/2010
		} else if (file.getName().endsWith(EXCEL_XLSX)) {
			wb = new XSSFWorkbook(in);
		}
		return wb;
	}

	/**
	 * 判断文件是否是excel
	 */
	public static void checkExcelVaild(File file) throws Exception {
		if (!file.exists()) {
			throw new Exception("文件不存在");
		}
		if (!(file.isFile() && (file.getName().endsWith(EXCEL_XLS) || file.getName().endsWith(EXCEL_XLSX)))) {
			throw new Exception("文件不是Excel");
		}
	}

	/**
	 * 读取Excel测试，兼容 Excel 2003/2007/2010
	 */
	public static Object getValue(Cell cell) {
		Object obj = null;
		if(cell == null) {
			return null;
		}
		CellType cellTypeEnum = cell.getCellTypeEnum();
		System.out.println(cellTypeEnum.name());
		switch (cell.getCellTypeEnum()) {
		case BOOLEAN:
			obj = cell.getBooleanCellValue();
			break;
		case ERROR:
			obj = cell.getErrorCellValue();
			break;
		case NUMERIC:
			DataFormatter dataFormatter = new DataFormatter();
			obj = dataFormatter.formatCellValue(cell);
			// 判断单元格是否属于日期格式
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				Date date = cell.getDateCellValue();
		        Instant instant = date.toInstant();
		        ZoneId zone = ZoneId.systemDefault();		       
		        obj = LocalDateTime.ofInstant(instant, zone);
			}
//			short dataFormat = cell.getCellStyle().getDataFormat();
//			SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
//			if (dataFormat != 0) {
//				// format == 176时是当指定单元格格式为日期是都是176
//				if (dataFormat == 14 || dataFormat == 31 || dataFormat == 57 || dataFormat == 58 || dataFormat == 176) {
//					// 日期
//					sdf = new SimpleDateFormat("yyyy-MM-dd");
//				} else if (dataFormat == 20 || dataFormat == 32) {
//					// 时间
//					sdf = new SimpleDateFormat("HH:mm");
//				}
//				double value = cell.getNumericCellValue();
//				Date date = DateUtil.getJavaDate(value);
//				obj = sdf.format(date);
//			}
			break;
		case STRING:
			obj = cell.getStringCellValue();
			break;
		default:
			break;
		}
		return obj;
	}
	
	public static String getValue1(Cell cell,String valueType) {
		if(cell == null) {
			return "";
		}
		CellType cellType = cell.getCellTypeEnum();
		if(cellType.name().equals(valueType)) {
			
		}
		return null;

	}	
	
	/**
	 * 获取合并单元格的内容
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public Object getMergedRegionValue(Sheet sheet ,int row , int column){
        int sheetMergeCount = sheet.getNumMergedRegions();
        for(int i = 0 ; i < sheetMergeCount ; i++){
            CellRangeAddress ca = sheet.getMergedRegion(i);
            int firstColumn = ca.getFirstColumn();
            int lastColumn = ca.getLastColumn();
            int firstRow = ca.getFirstRow();
            int lastRow = ca.getLastRow();
            if(row >= firstRow && row <= lastRow){
                if(column >= firstColumn && column <= lastColumn){
                    Row fRow = sheet.getRow(firstRow);
                    Cell fCell = fRow.getCell(firstColumn);
                    return getValue(fCell) ;
                }
            }
        }
        return null ;
    }
	
	/**
	 * 模板下载
	 */
	
	public static void TemplateDownload(HttpServletResponse request, HttpServletResponse response,String fileName,List<String> listName)
			throws Exception {
		if(fileName == null || "".equals(fileName)) {
			throw new RuntimeException("模板名称为空");
		}
		if(listName == null || listName.size()<1) {
			throw new RuntimeException("模板列名称为空");
		}
		HSSFWorkbook workbook = new HSSFWorkbook();
		HSSFSheet sheet = workbook.createSheet("sheet1");
		//HSSFCellStyle cellStyle = workbook.createCellStyle();
		//// 设置单元格水平方向对齐方式 居中
		//cellStyle.setAlignment(HorizontalAlignment.LEFT);
		HSSFCellStyle cellStyle2 = workbook.createCellStyle();
		// 设置字体样式
		HSSFFont font = workbook.createFont();
		font.setFontName("微软雅黑");
		// 设置字号
		font.setFontHeightInPoints((short) 14);
		cellStyle2.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		cellStyle2.setFillForegroundColor(IndexedColors.YELLOW.index);
		// 字体风格放入
		cellStyle2.setFont(font);

		// 设置表格默认列宽度为15个字节
//		sheet.setDefaultColumnWidth(15);
		
		HSSFRow filedsRow = sheet.createRow(0);
		for(int i =0;i<listName.size();i++) {
			HSSFCell cell = filedsRow.createCell(i);
			cell.setCellStyle(cellStyle2);
			HSSFRichTextString text0 = new HSSFRichTextString(listName.get(i));
			cell.setCellValue(text0);
		}

		// 清空response
		response.reset();
		// 设置response的Header
		// 告诉浏览器用什么软件可以打开此文件
		response.setContentType("application/octet-stream");
		response.addHeader("Content-Disposition",
				"attachment;filename=" + new String(fileName.getBytes("utf-8"), "ISO8859-1"));
		OutputStream out = response.getOutputStream();
		   	 //   response.setContentType("application/octet-stream;charset=utf-8");
		workbook.write(out);
		workbook.close();
		out.flush();
		out.close();
	}
}
